package ReefStats::Schema::ReefStatsDB::Maillog::ResultSet;

use strict;
use warnings;
use base 'DBIx::Class::ResultSet';
use DateTime;
use List::MoreUtils qw(mesh);

sub mail_statistics_reasons {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $user       = $arguments->{'user'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};
    my $interval   = $arguments->{'interval'} || 'day';

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    my @metrics = (
        [ blacklist        => Blacklist          => q~'Blacklist'~ ],
        [ spam_fingerprint => 'SPAM Fingerprint' => q~'SPAM Fingerprint'~ ],
        [
            invalid_recipient => 'Invalid Recipient' =>
              q~'Recipient Address Rejected','Invalid Recipient'~
        ],
        [
            intention_analysis => 'Intention Analysis' =>
              q~'Intention Analysis'~
        ],
        [
            header_filter_match => 'Header Filter Match' =>
              q~'Header Filter Match'~
        ],
        [
            barracuda_blocklist => 'Barracuda Blocklist' =>
              q~'Barracuda Blocklist'~
        ],
        [ timeout_exceeded => 'Timeout Exceeded' => q~'Timeout Exceeded'~ ],
        [ allowed          => Allowed            => q~'(Allowed)'~ ],
        [
            barracuda_whitelist => 'Barracuda Whitelist' =>
              q~'Barracuda Whitelist'~
        ],
        [
            recipient_whitelist => 'Recipient Whitelist' =>
q~'Sender Block/Accept', 'Sender Address Rejected') and action in ('Whitelist'~
        ],
        [
            other => 'Other' =>
q~'Domain Not Found', 'Mail Protocol Error', 'Sender Address Rejected', 'Sender Block/Accept', 'Subject Filter Match', 'Virus', 'Client IP', 'Banned Attachment', 'No Such Domain', ''~
        ],
        [ score => 'Score' => q~'Score'~ ],
        [
            recipient_blocked => 'Recipient Blocked' =>
              q~'Sender Block/Accept') and action in ('Blocked'~
        ],
    );

    $search_criteria->{cluster} = $cluster if $cluster;
    $search_criteria->{domain} = $domain
      if $domain and not $user;
    $search_criteria->{user} = $user if $user;

    my $table =
        ($user)   ? 'ActionsReasonsUser'
      : ($domain) ? 'ActionsReasonsDomain'
      :             'ActionsReasonsAll';

    my $maillog = $self->result_source->schema->resultset($table);

    #$interval = $interval . '(day)' unless $interval =~ /^day$/i;
    $interval = { $interval => 'day' } unless $interval =~ /^day$/i;

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select => [
                $interval,
                map {
                    {       sum => 'case when reason in ('
                          . $_->[2]
                          . ') then number else 0 end' }
                  } @metrics
            ],
            as       => [ 'interval', map { $_->[0] } @metrics ],
            group_by => [$interval],
            order_by => [qw( 1 )],
        }
    );

    return $rs;
}

sub mail_statistics_actions {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $user       = $arguments->{'user'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};
    my $interval   = $arguments->{'interval'} || 'day';

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;
    $search_criteria->{domain} = $domain
      if $domain and not $user;
    $search_criteria->{user} = $user if $user;

    my $table =
        ($user)   ? 'ActionsReasonsUser'
      : ($domain) ? 'ActionsReasonsDomain'
      :             'ActionsReasonsAll';

    $interval = { $interval => 'day' } unless $interval =~ /^day$/i;

    my $maillog = $self->result_source->schema->resultset($table);
    my $rs      = $maillog->search(
        {%$search_criteria},
        {
            select => [
                $interval,
                {
                    sum =>
"case when action in ('Blocked', 'Deferred') then number else 0 end"
                },
                {
                    sum =>
"case when action in ('Quarantined', 'Per-User Quarantine') then number else 0 end"
                },
                {
                    sum =>
"case when action in ('Allowed', 'Tagged') then number else 0 end"
                },
                {
                    sum =>
"case when action in ('Whitelisted') then number else 0 end"
                }
            ],
            as => [qw( interval blocked quarantined allowed whitelisted )],
            group_by => [$interval],
            order_by => [qw( 1 )],
        }
    );

    return $rs;
}

sub top_spammed_emails {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;
    $search_criteria->{domain} = $domain
      if $domain;

    $search_criteria->{reason} = 'SPAM Fingerprint';

    my $table = 'ActionsReasons';

    #    my $table = 'Maillog';

    my $maillog = $self->result_source->schema->resultset($table);

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select => [ 'user', { sum => 'number' }, ],

            #            select   => [ 'Recipient', { count => '*' }, ],
            as       => [qw( user messages)],
            group_by => [qw( user )],

            #            group_by => [qw( Recipient )],
            order_by => ['2 desc'],
        }
    );
    return $rs;
}

sub top_recipients {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;
    $search_criteria->{domain} = $domain
      if $domain;

    my $table = 'ActionsReasons';

    my $maillog = $self->result_source->schema->resultset($table);

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select => [ 'user', { sum => 'number' }, ],

            #            select   => [ 'Recipient', { count => '*' }, ],
            as       => [qw( user messages)],
            group_by => [qw( user )],

            #            group_by => [qw( Recipient )],
            order_by => ['2 desc'],
        }
    );
    return $rs;
}

sub top_domains_for_email {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;

    my $table = 'ActionsReasons';

    my $maillog = $self->result_source->schema->resultset($table);

    $search_criteria->{action} = [qw(Allowed Tagged Whitelisted)];

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select   => [ 'domain', { sum => 'number' }, ],
            as       => [qw( domain messages)],
            group_by => [qw( domain )],
            order_by => ['2 desc'],
        }
    );
    return $rs;
}

sub top_domains_for_spam {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;

    my $table = 'ActionsReasons';

    my $maillog = $self->result_source->schema->resultset($table);

    $search_criteria->{action} =
      [ qw(Blocked Deferred Quarantined), 'Per-User Quarantine' ];

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select   => [ 'domain', { sum => 'number' }, ],
            as       => [qw( domain messages)],
            group_by => [qw( domain )],
            order_by => ['2 desc'],
        }
    );
    return $rs;
}

sub top_actions {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;
    $search_criteria->{domain} = $domain
      if $domain;

    my $table = 'ActionsReasons';

    my $maillog = $self->result_source->schema->resultset($table);

    $search_criteria->{action} =
      [ qw(Blocked Deferred Quarantined), 'Per-User Quarantine' ];

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select => [
"case when action in ('Blocked', 'Quarantined') then 'Blocked' else action end",
                { sum => 'number' },
            ],
            as       => [qw( action messages)],
            group_by => [qw( action )],
            order_by => ['2 desc'],
        }
    );
    return $rs;
}

sub blacklists {
    my ( $self, $arguments ) = @_;

    my $cluster    = $arguments->{'cluster'};
    my $domain     = $arguments->{'domain'};
    my $user       = $arguments->{'user'};
    my $start_date = $arguments->{'start_date'};
    my $end_date   = $arguments->{'end_date'};
    my $interval   = $arguments->{'interval'} || 'day';

    my ( $upper_date, $lower_date );

    my @x = qw(month day year);
    if ($end_date) {
        my @y = split /\D/, $end_date;
        $upper_date = DateTime->new( mesh @x, @y );
    }
    else {
        $upper_date = DateTime->now()->subtract( days => 1 );
    }

    if ($start_date) {
        my @y = split /\D/, $start_date;
        $lower_date = DateTime->new( mesh @x, @y );
    }
    else {
        $lower_date = $upper_date->clone->subtract( days => 7 );
    }

    $upper_date->set_hour(23)->set_minute(59)->set_second(59)
      ->set_nanosecond(999999999);
    $lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

    my $search_criteria =
      { day => { -between => [ $lower_date, $upper_date ] }, };

    $search_criteria->{cluster} = $cluster if $cluster;
    $search_criteria->{domain} = $domain
      if $domain and not $user;
    $search_criteria->{user} = $user if $user;

    my $table = 'BlacklistedReasonexts';

    my $maillog = $self->result_source->schema->resultset($table);

    $interval = { $interval => 'day' } unless $interval =~ /^day$/i;

    my $rs = $maillog->search(
        {%$search_criteria},
        {
            select   => [ qw( reasonext ), { sum => 'number' }, ],
            as       => [qw( blacklist messages )],
            group_by => [qw(reasonext)],
            order_by => [qw( 1 )],
        }
    );

    return $rs;
}

1;

